﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Ylh.DBUtility;
using Ylh.IDAL;
using Ylh.Model;
using System.Data;
using System.Data.SqlClient;



namespace Ylh.SQLServerDAL
{
    public class StaffInfoDAL : IStaffInfo
    {
        /// <summary>
        /// 插入员工详细信息
        /// </summary>
        /// <param name="ConStr"></param>
        /// <param name="model">员工详细信息</param>
        /// <returns></returns>
        public int InsertIntoStaffInfo(string ConStr, Ylh.Model.TbStaffInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into TbStaffInfo(");
            strSql.Append("StaffID,Sex,Birdthday,Tel,Address,IDCard,InTime,Remark,TelPhone,LinkMan,LinkNum,Hometown,Marital,HomeAddress,QQ,mail)");
            strSql.Append(" values (");
            strSql.Append("@StaffID,@Sex,Null,Null,@Address,@IDCard,Null,@Remark,@TelPhone,Null,Null,@Hometown,@Marital,@HomeAddress,@QQ,@mail)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@StaffID", SqlDbType.Int,4),
					new SqlParameter("@Sex", SqlDbType.VarChar,5),
					new SqlParameter("@Address", SqlDbType.VarChar,50),
					new SqlParameter("@IDCard", SqlDbType.VarChar,30),
					new SqlParameter("@Remark", SqlDbType.NText),
					new SqlParameter("@TelPhone", SqlDbType.VarChar,20),
					new SqlParameter("@Hometown", SqlDbType.VarChar,20),
					new SqlParameter("@Marital", SqlDbType.VarChar,20),
					new SqlParameter("@HomeAddress", SqlDbType.VarChar,200),
					new SqlParameter("@QQ", SqlDbType.VarChar,20),
					new SqlParameter("@mail", SqlDbType.VarChar,50)};
            parameters[0].Value = model.StaffID;
            parameters[1].Value = model.Sex;
            parameters[2].Value = model.Address;
            parameters[3].Value = model.IDCard;
            parameters[4].Value = "这条记录为导入数据";
            parameters[5].Value = model.TelPhone;
            parameters[6].Value = model.Hometown;
            parameters[7].Value = model.Marital;
            parameters[8].Value = model.HomeAddress;
            parameters[9].Value = model.QQ;
            parameters[10].Value = model.mail;

            DataTable dt = SqlHelper.ExcuteQuery(ConStr, strSql.ToString(), parameters, CommandType.Text);
            return Convert.ToInt32(dt.Rows[0][0]);
        }


        /// <summary>
        /// 更新员工详细信息
        /// </summary>
        /// <param name="ConStr"></param>
        /// <param name="model">员工详细信息对象</param>
        /// <returns></returns>
        public bool UpdateStaffInfo(string ConStr, Ylh.Model.TbStaffInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update TbStaffInfo set ");
            strSql.Append("Sex=@Sex,");
            strSql.Append("Birdthday=@Birdthday,");
            strSql.Append("Tel=@Tel,");
            strSql.Append("Address=@Address,");
            strSql.Append("IDCard=@IDCard,");
            strSql.Append("InTime=@InTime,");
            strSql.Append("Remark=@Remark,");
            strSql.Append("TelPhone=@TelPhone,");
            strSql.Append("LinkMan=@LinkMan,");
            strSql.Append("LinkNum=@LinkNum,");
            strSql.Append("Hometown=@Hometown,");
            strSql.Append("Marital=@Marital,");
            strSql.Append("HomeAddress=@HomeAddress,");
            strSql.Append("QQ=@QQ,");
            strSql.Append("mail=@mail");
            strSql.Append(" where StaffID=@StaffID ");
            SqlParameter[] parameters = {
					new SqlParameter("@StaffInfoID", SqlDbType.Int,4),
					new SqlParameter("@StaffID", SqlDbType.Int,4),
					new SqlParameter("@Sex", SqlDbType.VarChar,5),
					new SqlParameter("@Birdthday", SqlDbType.DateTime),
					new SqlParameter("@Tel", SqlDbType.VarChar,20),
					new SqlParameter("@Address", SqlDbType.VarChar,50),
					new SqlParameter("@IDCard", SqlDbType.VarChar,30),
					new SqlParameter("@InTime", SqlDbType.DateTime),
					new SqlParameter("@Remark", SqlDbType.NText),
					new SqlParameter("@TelPhone", SqlDbType.VarChar,20),
					new SqlParameter("@LinkMan", SqlDbType.VarChar,20),
					new SqlParameter("@LinkNum", SqlDbType.VarChar,20),
					new SqlParameter("@Hometown", SqlDbType.VarChar,20),
					new SqlParameter("@Marital", SqlDbType.VarChar,20),
					new SqlParameter("@HomeAddress", SqlDbType.VarChar,200),
					new SqlParameter("@QQ", SqlDbType.VarChar,20),
					new SqlParameter("@mail", SqlDbType.VarChar,50)};
            parameters[0].Value = model.StaffInfoID;
            parameters[1].Value = model.StaffID;
            parameters[2].Value = model.Sex;
            parameters[3].Value = model.Birdthday;
            parameters[4].Value = model.Tel;
            parameters[5].Value = model.Address;
            parameters[6].Value = model.IDCard;
            parameters[7].Value = model.InTime;
            parameters[8].Value = model.Remark;
            parameters[9].Value = model.TelPhone;
            parameters[10].Value = model.LinkMan;
            parameters[11].Value = model.LinkNum;
            parameters[12].Value = model.Hometown;
            parameters[13].Value = model.Marital;
            parameters[14].Value = model.HomeAddress;
            parameters[15].Value = model.QQ;
            parameters[16].Value = model.mail;

            int i = SqlHelper.ExecuteNonQuery(ConStr, strSql.ToString(), parameters, CommandType.Text);

            if (i > 0) return true;
            return false;

        }





        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Ylh.Model.TbStaffInfo GetModel( string ConStr, int StaffID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 StaffInfoID,StaffID,Sex,Birdthday,Tel,Address,IDCard,InTime,Remark,TelPhone,LinkMan,LinkNum,Hometown,Marital,HomeAddress,QQ,mail from TbStaffInfo ");
            strSql.Append(" where StaffID=@StaffID ");
            SqlParameter[] parameters = {
					new SqlParameter("@StaffID", SqlDbType.Int,4)};
            parameters[0].Value = StaffID;

            Ylh.Model.TbStaffInfo model = new Ylh.Model.TbStaffInfo();

            DataTable dt=SqlHelper.ExcuteQuery(ConStr,strSql.ToString(), parameters,CommandType.Text);
            
            if (dt.Rows.Count > 0)
            {
                if (dt.Rows[0]["StaffInfoID"].ToString() != "")
                {
                    model.StaffInfoID = int.Parse(dt.Rows[0]["StaffInfoID"].ToString());
                }
                if (dt.Rows[0]["StaffID"].ToString() != "")
                {
                    model.StaffID = int.Parse(dt.Rows[0]["StaffID"].ToString());
                }
                model.Sex = dt.Rows[0]["Sex"].ToString();
                if (dt.Rows[0]["Birdthday"].ToString() != "")
                {
                    model.Birdthday = DateTime.Parse(dt.Rows[0]["Birdthday"].ToString());
                }
                model.Tel = dt.Rows[0]["Tel"].ToString();
                model.Address = dt.Rows[0]["Address"].ToString();
                model.IDCard = dt.Rows[0]["IDCard"].ToString();
                if (dt.Rows[0]["InTime"].ToString() != "")
                {
                    model.InTime = DateTime.Parse(dt.Rows[0]["InTime"].ToString());
                }
                model.Remark = dt.Rows[0]["Remark"].ToString();
                model.TelPhone = dt.Rows[0]["TelPhone"].ToString();
                model.LinkMan = dt.Rows[0]["LinkMan"].ToString();
                model.LinkNum = dt.Rows[0]["LinkNum"].ToString();
                model.Hometown = dt.Rows[0]["Hometown"].ToString();
                model.Marital =dt.Rows[0]["Marital"].ToString();
                model.HomeAddress = dt.Rows[0]["HomeAddress"].ToString();
                model.QQ = dt.Rows[0]["QQ"].ToString();
                model.mail = dt.Rows[0]["mail"].ToString();
                return model;
            }
            else
            {
                return null;
            }
        }



        /// <summary>
        /// 插入员工详细信息
        /// </summary>
        /// <param name="ConStr"></param>
        /// <param name="model">员工详细信息</param>
        /// <returns></returns>
        public int InsertIntoStaffInfo1(string ConStr, Ylh.Model.TbStaffInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into TbStaffInfo(");
            strSql.Append("StaffID,Sex,Birdthday,Tel,Address,IDCard,InTime,Remark,TelPhone,LinkMan,LinkNum,Hometown,Marital,HomeAddress,QQ,mail)");
            strSql.Append(" values (");
            strSql.Append("@StaffID,@Sex,@Birdthday,@Tel,@Address,@IDCard,@InTime,@Remark,@TelPhone,@LinkMan,@LinkNum,@Hometown,@Marital,@HomeAddress,@QQ,@mail)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@StaffID", SqlDbType.Int,4),
					new SqlParameter("@Sex", SqlDbType.VarChar,5),
					new SqlParameter("@Birdthday", SqlDbType.DateTime),
					new SqlParameter("@Tel", SqlDbType.VarChar,20),
					new SqlParameter("@Address", SqlDbType.VarChar,50),
					new SqlParameter("@IDCard", SqlDbType.VarChar,30),
					new SqlParameter("@InTime", SqlDbType.DateTime),
					new SqlParameter("@Remark", SqlDbType.NText),
					new SqlParameter("@TelPhone", SqlDbType.VarChar,20),
					new SqlParameter("@LinkMan", SqlDbType.VarChar,20),
					new SqlParameter("@LinkNum", SqlDbType.VarChar,20),
					new SqlParameter("@Hometown", SqlDbType.VarChar,20),
					new SqlParameter("@Marital", SqlDbType.VarChar,20),
					new SqlParameter("@HomeAddress", SqlDbType.VarChar,200),
					new SqlParameter("@QQ", SqlDbType.VarChar,20),
					new SqlParameter("@mail", SqlDbType.VarChar,50)};
            parameters[0].Value = model.StaffID;
            parameters[1].Value = model.Sex;
            parameters[2].Value = model.Birdthday;
            parameters[3].Value = model.Tel;
            parameters[4].Value = model.Address;
            parameters[5].Value = model.IDCard;
            parameters[6].Value = model.InTime;
            parameters[7].Value = model.Remark;
            parameters[8].Value = model.TelPhone;
            parameters[9].Value = model.LinkMan;
            parameters[10].Value = model.LinkNum;
            parameters[11].Value = model.Hometown;
            parameters[12].Value = model.Marital;
            parameters[13].Value = model.HomeAddress;
            parameters[14].Value = model.QQ;
            parameters[15].Value = model.mail;

            DataTable dt = SqlHelper.ExcuteQuery(ConStr, strSql.ToString(), parameters, CommandType.Text);
            return Convert.ToInt32(dt.Rows[0][0]);
        }


    }
}
